package com.huiyuanai.pojo.provider;

import com.huiyuanai.dao.Provider;
import com.huiyuanai.util.BaseUtil;
import com.mysql.cj.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: liwan
 * @Description:
 * @Date: create in 2021/10/17
 */
public class ProviderPojoImpl implements ProviderPojo{

    @Override
    public List<Provider> getProvider(Connection connection, String proCode, String proName, String id, int beginPage, int endPage) throws SQLException {
        //定义需要传参的变量
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Object> param = new ArrayList<Object>();

        //返回参数
        List<Provider> providersList = new ArrayList<Provider>();

        //动态sql
        if (connection != null) {
            String sql = "select * from smbms_provider where 1 = 1";
            if (!StringUtils.isNullOrEmpty(proCode)) {
                sql += " and proCode like ?";
                param.add("%" + proCode + "%");
            }
            if (!StringUtils.isNullOrEmpty(proName)) {
                sql += " and proName like ?";
                param.add("%" + proName + "%");
            }
            if (!StringUtils.isNullOrEmpty(id)) {
                sql += " and id = ?";
                param.add(id);
            }
            if (beginPage > 0 && endPage > 0) {
                sql += "limt ? , ?";
                param.add(beginPage);
                param.add(endPage);
            }

            resultSet = BaseUtil.execute(connection, sql, param.toArray(), resultSet, preparedStatement);

            //循环取值
            while (resultSet.next()) {
                Provider provider = new Provider();
                provider.setId(resultSet.getInt("id"));
                provider.setProCode(resultSet.getString("proCode"));
                provider.setProName(resultSet.getString("proName"));
                provider.setProDesc(resultSet.getString("proDesc"));
                provider.setProContact(resultSet.getString("proContact"));
                provider.setProPhone(resultSet.getString("proPhone"));
                provider.setProAddress(resultSet.getString("proAddress"));
                provider.setProFax(resultSet.getString("proFax"));
                provider.setCreatedBy(resultSet.getInt("createdBy"));
                provider.setCreationDate(resultSet.getDate("creationDate"));
                provider.setModifyDate(resultSet.getDate("modifyDate"));
                provider.setModifyBy(resultSet.getInt("modifyBy"));
                providersList.add(provider);
            }
        }
        //释放空间
        BaseUtil.clostResour(null, preparedStatement, resultSet);
        return providersList;
    }

    @Override
    public int getCount(Connection connection, String proCode, String proName) throws SQLException {
        PreparedStatement preparedStatement = null;
        List<Object> param = new ArrayList<Object>();
        ResultSet resultSet = null;
        int result = 0;

        if (connection != null) {
            String sql = "select count(*) as count from smbms_provider where 1 = 1";
            if (!StringUtils.isNullOrEmpty(proCode)) {
                sql += " and proCode = ?";
                param.add(proCode);
            }
            if (!StringUtils.isNullOrEmpty(proName)) {
                sql += " and proName = ?";
                param.add(proName);
            }

            resultSet = BaseUtil.execute(connection, sql, param.toArray(),resultSet, preparedStatement);
            while (resultSet.next()) {
                result = resultSet.getInt("count");
            }
        }
        BaseUtil.clostResour(null, preparedStatement, resultSet);
        return result;
    }

    @Override
    public int upDataProvider(Connection connection, Provider provider) throws SQLException {
        PreparedStatement preparedStatement = null;
        int result = 0;
        List<Object> list = new ArrayList<Object>();

        if (connection != null) {
            String sql = "update smbms_provider set proCode = ?, proName = ?, proDesc = ?, proContact = ?, proPhone = ?, proAddress = ?, proFax = ?, createdBy = ?, creationDate = ? where id = ?";
            list.add(provider.getProCode());
            list.add(provider.getProName());
            list.add(provider.getProDesc());
            list.add(provider.getProContact());
            list.add(provider.getProPhone());
            list.add(provider.getProAddress());
            list.add(provider.getProFax());
            list.add(provider.getModifyBy());
            list.add(provider.getModifyDate());
            list.add(provider.getId());

            result = BaseUtil.execute(connection, sql, list.toArray(), preparedStatement);
            BaseUtil.clostResour(null, preparedStatement, null);
        }
        return result;
    }

    @Override
    public int addProvider(Connection connection, Provider provider) throws SQLException {
        PreparedStatement preparedStatement = null;
        List<Object> list = new ArrayList<Object>();
        int result = 0;

        if (connection != null) {
            String sql = "insert into smbms_provider (proCode, proName, proDesc, proContact, proPhone, proAddress, proFax, createdBy, creationDate) values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
            list.add(provider.getProCode());
            list.add(provider.getProName());
            list.add(provider.getProDesc());
            list.add(provider.getProContact());
            list.add(provider.getProPhone());
            list.add(provider.getProAddress());
            list.add(provider.getProFax());
            list.add(provider.getCreatedBy());
            list.add(provider.getCreationDate());

            result = BaseUtil.execute(connection, sql, list.toArray(), preparedStatement);
            System.out.println("这里" + result);
        }
        BaseUtil.clostResour(null, preparedStatement, null);
        return result;
    }

    @Override
    public int delProvider(Connection connection, String id) throws SQLException {
        PreparedStatement preparedStatement = null;
        List<Object> list = new ArrayList<Object>();
        int result = 0;

        if (connection != null) {
            String sql = "delete from smbms_provider where id = ?";
            list.add(id);
            result = BaseUtil.execute(connection, sql, list.toArray(), preparedStatement);
        }

        BaseUtil.clostResour(null, preparedStatement, null);
        return result;
    }
}
